﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;

namespace Sinacor.Infra.Tools
{
    public class DataAccess
    {
        public List<Action> SelectAction(Action entity)
        {
            List<Action> response = new List<Action>();
            IDbCommand command = null;

            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT ");
            sql.AppendLine("    COD_EXEC_SERV, ");
            sql.AppendLine("    NOME_EXEC_SERV, ");
            sql.AppendLine("    NOME_MET_BASI, ");
            sql.AppendLine("    NOME_MET_ADIC, ");
            sql.AppendLine("    COD_TIPO_MET_EXEC ");
            sql.AppendLine("FROM ");
            sql.AppendLine("    TFWEXEC_SERV ");            

            try
            {
                using (command = this.GetSqlStringCommand(sql.ToString()))
                {
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            response.Add(
                                new Action
                                {
                                    ActionId = Convert.ToInt32(reader["COD_EXEC_SERV"]),
                                    ActionName = reader["NOME_EXEC_SERV"].ToString(),
                                    MethodName = reader["NOME_MET_BASI"].ToString(),
                                    MethodNameKernel = reader["NOME_MET_ADIC"].ToString(),
                                    IsKernelPlus = reader["COD_TIPO_MET_EXEC"].ToString() == "S"
                                }
                            );
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null && command.Connection != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close(); 
            }

            return response;
        }

        public Action InsertAction(Action entity)
        {
            StringBuilder sql = new StringBuilder();
            IDbCommand command = null;

            sql.AppendLine("INSERT INTO TFWEXEC_SERV ( ");
            sql.AppendLine("    COD_EXEC_SERV, ");
            sql.AppendLine("    NOME_EXEC_SERV, ");
            sql.AppendLine("    NOME_MET_BASI, ");
            sql.AppendLine("    NOME_MET_ADIC, ");
            sql.AppendLine("    COD_TIPO_MET_EXEC ");
            sql.AppendLine("    ) VALUES ( ");
            sql.AppendLine("    :COD_EXEC_SERV, ");
            sql.AppendLine("    :NOME_EXEC_SERV, ");
            sql.AppendLine("    :NOME_MET_BASI, ");
            sql.AppendLine("    :NOME_MET_ADIC, ");
            sql.AppendLine("    :COD_TIPO_MET_EXEC ) ");

            try
            {
                using (command = this.GetSqlStringCommand(sql.ToString()))
                {
                    this.AddInParameter(command, "COD_EXEC_SERV", DbType.Int32, GetNextActionId());
                    this.AddInParameter(command, "NOME_EXEC_SERV", DbType.String, entity.ActionName);
                    this.AddInParameter(command, "NOME_MET_BASI", DbType.String, entity.MethodName);
                    this.AddInParameter(command, "NOME_MET_ADIC", DbType.String, entity.MethodNameKernel);
                    this.AddInParameter(command, "COD_TIPO_MET_EXEC", DbType.String, entity.IsKernelPlus ? "S" : "N");

                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null && command.Connection != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }

            return entity;
        }

        public void UpdateAction(Action entity)
        {
            StringBuilder sql = new StringBuilder();
            IDbCommand command = null;

            sql.AppendLine("UPDATE TFWEXEC_SERV SET ");            
            sql.AppendLine("NOME_EXEC_SERV = :NOME_EXEC_SERV, ");
            sql.AppendLine("NOME_MET_BASI = :NOME_MET_BASI, ");
            sql.AppendLine("NOME_MET_ADIC = :NOME_MET_ADIC, ");
            sql.AppendLine("COD_TIPO_MET_EXEC = :COD_TIPO_MET_EXEC ");
            sql.AppendLine("WHERE COD_EXEC_SERV = :COD_EXEC_SERV ");
            
            try
            {
                using (command = this.GetSqlStringCommand(sql.ToString()))
                {
                    this.AddInParameter(command, "COD_EXEC_SERV", DbType.Int32, entity.ActionId);
                    this.AddInParameter(command, "NOME_EXEC_SERV", DbType.String, entity.ActionName);
                    this.AddInParameter(command, "NOME_MET_BASI", DbType.String, entity.MethodName);
                    this.AddInParameter(command, "NOME_MET_ADIC", DbType.String, entity.MethodNameKernel);
                    this.AddInParameter(command, "COD_TIPO_MET_EXEC", DbType.String, entity.IsKernelPlus ? "S" : "N");

                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null && command.Connection != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }
        }

        public int DeleteAction(List<Action> entitys)
        {
            string sql = "DELETE FROM TFWEXEC_SERV WHERE COD_EXEC_SERV = :COD_EXEC_SERV";
            IDbCommand command = null;
            int linhasAfetadas = 0;

            try
            {
                foreach (Action entity in entitys)
                {
                    using (command = this.GetSqlStringCommand(sql))
                    {
                        this.AddInParameter(command, "COD_EXEC_SERV", DbType.Int32, entity.ActionId);
                        linhasAfetadas += command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (command != null && command.Connection != null && command.Connection.State == ConnectionState.Open)
                    command.Connection.Close();
            }

            return linhasAfetadas;
        }

        public int GetNextActionId()
        {
            int retorno = 0;
            string sql = " SELECT NVL(MAX(COD_EXEC_SERV) + 1, 1) FROM TFWEXEC_SERV ";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                retorno = Convert.ToInt32(command.ExecuteScalar());
            }

            return retorno;
        }

        private IDbCommand GetSqlStringCommand(string comando)
        {
            System.Data.OracleClient.OracleConnection _connection = new System.Data.OracleClient.OracleConnection();
            IDbCommand cmd = _connection.CreateCommand();
            cmd.CommandText = comando;
            cmd.CommandType = CommandType.Text;
            cmd.Connection.ConnectionString = ConfigurationSettings.AppSettings["connectionString"].ToString();
            cmd.Connection.Open();
            return cmd;
        }

        private void AddInParameter(IDbCommand cmd, string nameParameter, DbType ParameterParameter, object valueParameter)
        {
            IDbDataParameter parameter = cmd.CreateParameter();

            parameter.ParameterName = nameParameter;
            parameter.DbType = ParameterParameter;
            parameter.Direction = ParameterDirection.Input;

            if (valueParameter != null && !string.IsNullOrEmpty(valueParameter.ToString()))
                parameter.Value = valueParameter;
            else
                parameter.Value = DBNull.Value;

            cmd.Parameters.Add(parameter);
        }

        private string AddCharacterLike(string valueParameter)
        {
            string value = null;

            if (!string.IsNullOrEmpty(valueParameter) && !valueParameter.Contains('%'))
                return value = "%" + valueParameter.Replace(' ', '%') + "%";

            return value;
        }
    }
}